﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;

namespace LdExpress
{
    public class UserObjectsLog
    {
        public long ID = -1;
        public long UserID = 0;
        public long ObjectID = 0;
        public long ActionAdd = 0;
        public long ActionEdit = 0;
        public long ActionDelete = 0;
        public string LastAction = "";
        public string Description = "";
        public DateTime LastUpdate = DateTime.Now;

        public UserObjectsLog()
        {
        }

        public UserObjectsLog(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_userobjectslog";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`UserID`" +
                             ",`ObjectID`" +
                             ",`ActionAdd`" +
                             ",`ActionEdit`" +
                             ",`ActionDelete`" +
                             ",`LastAction`" +
                             ",`Description`" +
                              ",`LastUpdate`" +
                             ") " +
                         " values(" +
                             UserID.ToString() +
                             "," + ObjectID.ToString() +
                             "," + ActionAdd.ToString() +
                             "," + ActionEdit.ToString() +
                             "," + ActionDelete.ToString() +
                             ",'" + DBModule.RefineString(LastAction) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                         "UserID = " + UserID.ToString() +
                         ",ObjectID = " + ObjectID.ToString() +
                         ",ActionAdd = " + ActionAdd.ToString() +
                         ",ActionEdit = " + ActionEdit.ToString() +
                         ",ActionDelete = " + ActionDelete.ToString() +
                         ",LastAction='" + DBModule.RefineString(LastAction) + "'" +
                         ",Description='" + DBModule.RefineString(Description) + "'" +
                         ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_userobjectslog WHERE ID = " + this.ID.ToString() + " limit 1";

            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_userobjectslog WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("UserID")) UserID = long.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("ObjectID")) ObjectID = long.Parse(dr["ObjectID"].ToString());
                if (!dr.IsNull("ActionAdd")) ActionAdd = long.Parse(dr["ActionAdd"].ToString());
                if (!dr.IsNull("ActionEdit")) ActionEdit = long.Parse(dr["ActionEdit"].ToString());
                if (!dr.IsNull("ActionDelete")) ActionDelete = long.Parse(dr["ActionDelete"].ToString());
                if (!dr.IsNull("LastAction")) LastAction = dr["LastAction"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }

        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_userobjectslog where 1=1 ";
            if ((strCondition != "") && (strCondition != null))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void TotalActionUserObjectLog(string strCondition, string userID, string objectID, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT SUM(ActionAdd) as ActionAdd,SUM(ActionEdit) as ActionEdit,SUM(ActionDelete) AS ActionDelete FROM tbl_userobjectslog WHERE  1=1";
            strSQL += " AND UserID= " + userID + " AND ObjectID=" + objectID + " ";
            if ((strCondition != "") && (strCondition != null))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public int ListCount(string strCondition, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            int nCount = 0;
            // build SQL statement
            strSQL = "Select max(ID) as count from tbl_userobjectslog where 1=1 ";
            if ((strCondition != "") && (strCondition != null))
            {
                strSQL += strCondition;
            }
            string strnCount = DBModule.ExecuteQueryForOneResult(strSQL, cn, trans);
            if (!string.IsNullOrEmpty(strnCount))
                nCount = int.Parse(strnCount);
            return nCount;
        }
        public long GetLogID(string userID, string objectID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            long nCount = 0;
            // build SQL statement
            strSQL = "Select Max(ID) as ID from tbl_userobjectslog where UserID= " + userID + " AND ObjectID=" + objectID;
            string strnCount = DBModule.ExecuteQueryForOneResult(strSQL, cn, trans);
            if (!string.IsNullOrEmpty(strnCount))
                nCount = long.Parse(strnCount);
            return nCount;
        }
        public long GetLogIDByTime(string userID, string objectID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            long retVal = 0;
            // build SQL statement
            strSQL = "Select ID as ID from tbl_userobjectslog where UserID= " + userID + " AND ObjectID=" + objectID + " AND LastUpdate =" + DBModule.RefineDatetime(DateTime.Now);
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                retVal = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
            return retVal;
        }
        public void DoSearch(string strCondition, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Select `ID` from tbl_userobjectslog  where 1=1 ";
            if ((strCondition != "") && (strCondition != null))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static void SaveObjectActionLog(long userId, string objectCode, string actionName, MySqlConnection cn, MySqlTransaction trans)
        {
            if (userId > 0)
            {
                ObjectsFields objfield = new ObjectsFields();
                long objfieldID = objfield.GetIDbyCode(objectCode, GV.cn, GV.trans);
                UserObjectsLog objlog = new UserObjectsLog();
                long objlogID = objlog.GetLogIDByTime(userId.ToString(), objfieldID.ToString(), GV.cn, GV.trans);
                if (objlogID > 0)
                {
                    objlog.ID = objlogID;
                    objlog.Load(GV.cn, GV.trans);
                }
                objlog.UserID = userId;
                objlog.ObjectID = objfieldID;
                if (actionName.ToLower().CompareTo("add") == 0)
                    objlog.ActionAdd = objlog.ActionAdd + 1;
                else if (actionName.ToLower().CompareTo("edit") == 0)
                    objlog.ActionEdit = objlog.ActionEdit + 1;
                else if (actionName.ToLower().CompareTo("delete") == 0)
                    objlog.ActionDelete = objlog.ActionDelete + 1;
                objlog.LastAction = actionName;
                objlog.LastUpdate = DateTime.Now;
                objlog.Save(GV.cn, GV.trans);
            }
        }

        public static void SaveObjectActionLog(string objectCode, string actionName, MySqlConnection cn, MySqlTransaction trans)
        {
            long userId = 0;
            if (HttpContext.Current.Session["logined_id"] != null && HttpContext.Current.Session["logined_id"].ToString().Length > 0)
                userId = long.Parse(HttpContext.Current.Session["logined_id"].ToString());
            if (userId > 0)
            {
                ObjectsFields objfield = new ObjectsFields();
                long objfieldID = objfield.GetIDbyCode(objectCode, GV.cn, GV.trans);
                UserObjectsLog objlog = new UserObjectsLog();
                long objlogID = objlog.GetLogIDByTime(userId.ToString(), objfieldID.ToString(), GV.cn, GV.trans);
                if (objlogID > 0)
                {
                    objlog.ID = objlogID;
                    objlog.Load(GV.cn, GV.trans);
                }
                objlog.UserID = userId;
                objlog.ObjectID = objfieldID;
                if (actionName.ToLower().CompareTo("add") == 0)
                    objlog.ActionAdd = objlog.ActionAdd + 1;
                else if (actionName.ToLower().CompareTo("edit") == 0)
                    objlog.ActionEdit = objlog.ActionEdit + 1;
                else if (actionName.ToLower().CompareTo("delete") == 0)
                    objlog.ActionDelete = objlog.ActionDelete + 1;
                objlog.LastAction = actionName;
                objlog.LastUpdate = DateTime.Now;
                objlog.Save(GV.cn, GV.trans);
            }
        }
    }
}

